package com.bionic.banking.dao.classes;

import com.bionic.banking.core.entity.BankAccount;
import com.bionic.banking.core.entity.Customer;
import com.bionic.banking.dao.ICustomerDao;
import com.bionic.banking.dao.connection.DBQueryManager;
import java.sql.*;
import java.util.List;

public class CustomerDaoImpl implements ICustomerDao {
    
    protected final String SQL_SELECT = "SELECT * FROM " + getTableName();
    protected final String SQL_INSERT = "INSERT INTO " + getTableName()
       + "(first_name, last_name, middle_name, age, gender, phone, fax, email, user_id, address_id, image)"
       + " VALUES(?,?,?,?,?,?,?,?,?,?,?) "; //LOAD_FILE(?)
    protected final String SQL_UPDATE = "UPDATE " + getTableName()
            + " SET first_name=?,last_name=?,middle_name=?, "
            + " age=?, gender=?, phone=?, fax=?, email=?, user_id=?, address_id=?, image=? ";
    protected final String SQL_DELETE = "DELETE FROM " + getTableName();
    protected final String SQL_WHERE = " WHERE " + getKeyColumn() + "=?";
    protected static final int COLUMN_CUSTOMERID = 1;
    protected static final int COLUMN_FIRSTNAME = 2;
    protected static final int COLUMN_LASTNAME = 3;
    protected static final int COLUMN_MIDDLENAME = 4;
    protected static final int COLUMN_AGE = 5;
    protected static final int COLUMN_GENDER = 6;
    protected static final int COLUMN_PHONE = 7;
    protected static final int COLUMN_FAX = 8;
    protected static final int COLUMN_EMAIL = 9;
    protected static final int COLUMN_USERID = 10;
    protected static final int COLUMN_ADDRESSID = 11;
    protected static final int COLUMN_IMAGE = 12;
    protected static final int COLUMN_CREATEDON = 13;
    
    protected int maxRows;

    @Override
    public String getTableName() {
        return "customer";
    }

    @Override
    public String getKeyColumn() {
        return "CUSTOMER_ID";
    }

    @Override
    public DBQueryManager<Customer> getDBManager() {
        return new DBQueryManager<>(this);
    }

    @Override
    public Customer populateDto(ResultSet rs) throws SQLException {
        Customer dto = new Customer();
        dto.setCustomerId(rs.getInt(COLUMN_CUSTOMERID));
        dto.setFirstName(rs.getString(COLUMN_FIRSTNAME));
        dto.setLastName(rs.getString(COLUMN_LASTNAME));
        dto.setMiddleName(rs.getString(COLUMN_MIDDLENAME));
        dto.setAge(rs.getInt(COLUMN_AGE));
        dto.setGender(rs.getBoolean(COLUMN_GENDER));
        dto.setFax(rs.getString(COLUMN_FAX));
        dto.setPhone(rs.getString(COLUMN_PHONE));
        dto.setEmail(rs.getString(COLUMN_EMAIL));
        dto.setUserId(rs.getInt(COLUMN_USERID));
        dto.setAddressId(rs.getInt(COLUMN_ADDRESSID));        
        dto.setCreatedOn(rs.getTimestamp(COLUMN_CREATEDON));   
        byte[] image = rs.getBytes(COLUMN_IMAGE);
        if(image != null)
            dto.setImage(rs.getBytes(COLUMN_IMAGE));      
        
        return dto;
    }
    
    @Override
    public Integer create(Customer customer) throws SQLException {
        Object[] params = new Object[]{
            customer.getFirstName(),
            customer.getLastName(),
            customer.getMiddleName(),
            customer.getAge(),
            customer.getGender(),
            customer.getPhone(),
            customer.getFax(),
            customer.getEmail(),
            customer.getUserId(),
            customer.getAddressId(),
            customer.getImage()
        };
        Integer newCustomerId = getDBManager().executeCreateQuery(SQL_INSERT, params);
        customer.setCustomerId(newCustomerId);//update with new generated PK in DB 
        return newCustomerId;
    }

    @Override
    public boolean update(Customer customer) throws SQLException {
        final String query = SQL_UPDATE + SQL_WHERE;

        Object[] params = new Object[]{
            customer.getFirstName(),
            customer.getLastName(),
            customer.getMiddleName(),
            customer.getAge(),
            customer.getGender(),
            customer.getPhone(),
            customer.getFax(),
            customer.getEmail(),
            customer.getUserId(),
            customer.getAddressId(),
            customer.getImage(),
            customer.getCustomerId()
        };
        return getDBManager().executeSaveQuery(query, params);
    }

    @Override
    public void remove(int id) throws SQLException {
        final String query = SQL_DELETE + SQL_WHERE;
        getDBManager().executeRemoveQuery(query, id);
    }

    @Override
    public Customer findById(int customerid) throws SQLException {
        final String query = SQL_SELECT + SQL_WHERE;
        Object[] params = new Object[]{new Integer(customerid)};
        return getDBManager().executeSelectSingleQuery(query, params);
    }

    @Override
    public List<Customer> findByUserid(int userid) throws SQLException {
        final String query = SQL_SELECT + " WHERE USER_ID = ?";
        Object[] params = new Object[]{userid};
        return getDBManager().executeSelectQuery(query, params);        
    }

    @Override
    public List<Customer> findByEmail(String email) throws SQLException {
        final String query = SQL_SELECT + " WHERE EMAIL = ?";
        Object[] params = new Object[]{email};
        return getDBManager().executeSelectQuery(query, params);        
    }

    @Override
    public List<Customer> findByFirstNameAndLastName(String firtsname, String lastname) throws SQLException
    {
        final String query = SQL_SELECT + " WHERE first_name = ? and last_name = ? ";
        Object[] params = new Object[]{firtsname, lastname};
        return getDBManager().executeSelectQuery(query, params);        
    }
    
    @Override
    public List<Customer> findAll() throws SQLException {
        String query = SQL_SELECT;
        return getDBManager().executeSelectQuery(query, null);
    }

    @Override
    public List<BankAccount> findBankAccounts() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void setMaxRows(int maxRows) {
        this.maxRows = maxRows;
    }

    @Override
    public int getMaxRows() {
        return maxRows;
    }
}